package weaver.splittable.db.oracle;

import weaver.splittable.db.STCreateTableUtil;
import weaver.splittable.db.STDBConstants;

/**
 * 建表工具的Oracle实现
 * author: YUEYANG 2023-01-12
 */
public class STOracleCreateTableUtil implements STCreateTableUtil {

    // 创建 数据变动记录 表
    private static final String CREATE_DATA_CHANGE_TABLE_SQL =
            "CREATE TABLE "+ STDBConstants.DATA_CHANGE_TABLE_NAME.toUpperCase() +" (\n" +
            "id number NOT NULL primary key ,\n" +
            "event_type VARCHAR2(32) NOT NULL,\n" +
            "table_name VARCHAR2(64) NOT NULL,\n" +
            "data_id_column VARCHAR2(64) NOT NULL,\n" +
            "data_id VARCHAR2(32) NOT NULL,\n" +
            "synced CHAR(1) NOT NULL,\n" +
            "create_time VARCHAR2(64) NOT NULL,\n" +
            "sync_time VARCHAR2(64) NULL\n" +
            ")\n";

    // 创建 数据清理记录 表
    private static final String CREATE_DATA_CLEAR_TABLE_SQL =
            "CREATE TABLE "+ STDBConstants.DATA_CLEAR_TABLE_NAME.toUpperCase() +" (\n" +
            "id number NOT NULL primary key ,\n" +
            "table_name VARCHAR2(64) NOT NULL,\n" +
            "data_id_column VARCHAR2(64) NOT NULL,\n" +
            "data_id VARCHAR2(32) NOT NULL,\n" +
            "synced CHAR(1) NOT NULL,\n" +
            "clear_time VARCHAR2(64) NOT NULL,\n" +
            "sync_time VARCHAR2(64) NULL\n" +
            ")\n";

    //创建阈值表
    private static final String CREATE_DATA_THRESHOLD_TABLE_SQL =
            "CREATE TABLE " + STDBConstants.DATA_THRESHOLD_TABLE_NAME.toUpperCase() + " (\n" +
                    " id number NOT NULL primary key,\n" +
                    " requestid_threshold number NOT NULL,\n" +
                    " createdate_threshold VARCHAR2(64) NOT NULL,\n" +
                    " module VARCHAR2(64) NOT NULL,\n" +
                    " updatetime VARCHAR2(64) NOT NULL \n" +
                    ")\n";

    private static final String CREATE_FULL_DATA_TABLE_SQL = "create table %s AS select * from %s";

    @Override
    public String createDataChangeTable() {
        return CREATE_DATA_CHANGE_TABLE_SQL;
    }

    @Override
    public String createDataClearTable() {
        return CREATE_DATA_CLEAR_TABLE_SQL;
    }

    @Override
    public String createDataThresholdTable() {
        return CREATE_DATA_THRESHOLD_TABLE_SQL;
    }


    @Override
    public String createFullDataTable(String tableName) {
        String fullDataTableName = tableName + "_all";
        fullDataTableName = fullDataTableName.toUpperCase();
        tableName = tableName.toUpperCase();
        return String.format(CREATE_FULL_DATA_TABLE_SQL, fullDataTableName, tableName);
    }

    /**
     * 创建Oracle sequence
     * @param sequenceName 序列名
     * @return 创建SQL
     */
    public String createSequence(String sequenceName) {
        return "create sequence "+ sequenceName +"\n" +
                "minvalue 1\n" +
                "maxvalue 999999999999999999999999999\n" +
                "start with 21\n" +
                "increment by 1\n" +
                "cache 20\n";
    }

    /**
     * 创建Oracle 序列的触发器
     * @param triggerName 触发器名称
     * @param tableName 表名
     * @return 创建SQL
     */
    public String createSequenceTrigger(String triggerName, String sequenceName, String tableName) {
        return "create or replace trigger "+ triggerName +"\n" +
                "before insert on "+ tableName +"\n" +
                "for each row\n" +
                "begin\n" +
                "select "+ sequenceName +".nextval into :new.id from dual;\n" +
                "end;";
    }
}
